﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace UygulamaYetkiService
{
    public class GrupYetkiDAO : IDisposable
    {
        public GrupYetki GrupYetkiKaydet(GrupYetki grupYetki)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = @"insert into GrupYetki with(updlock) (GrupRef,FonksiyonRef) values (@grupRef,@fonksiyonRef); SELECT SCOPE_IDENTITY()";
                        cmd.Parameters.Add("@grupRef", grupYetki.GrupID);
                        cmd.Parameters.Add("@fonksiyonRef", grupYetki.FonksiyonID);
                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        grupYetki.ID = Convert.ToInt64(cmd.ExecuteScalar());
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return grupYetki;
                }
            }
        }

        public GrupYetki GrupYetkiGuncelle(GrupYetki grupYetki)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = @"update GrupYetki with(updlock) set GrupRef=@grupRef,FonksiyonRef=@fonksiyonRef where ID=@ID";
                        cmd.Parameters.Add("@grupRef", grupYetki.GrupID);
                        cmd.Parameters.Add("@fonksiyonRef", grupYetki.FonksiyonID);
                        cmd.Parameters.Add("@ID", grupYetki.ID);
                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        cmd.ExecuteScalar();
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return grupYetki;
                }
            }
        }

        public bool GrupYetkiSil(GrupYetki grupYetki)
        {
            bool sonuc = false;
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = @"delete from GrupYetki where ID=@ID  ";
                        cmd.Parameters.Add("@ID", grupYetki.ID);
                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        cmd.ExecuteScalar();
                        transaction.Commit();
                        sonuc = true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return sonuc;
                }
            }
        }

        public List<GrupYetki> GrupYetkiGetir()
        {
            List<GrupYetki> grupYetkiList = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = string.Format(@"   select gy.ID,
                                                           g.ID as GrupID,
                                                           g.Adi as GrupAdi,
                                                           f.ID as FonksiyonID,
                                                           f.Adi as FonksiyonAdi 
                                                    from Grup g with(nolock)
                                                    inner join GrupYetki gy with(nolock) on gy.GrupRef = g.ID
                                                    inner join Fonksiyon f with(nolock) on f.ID=gy.fonksiyonRef
                                                    order by g.Adi,f.Adi");
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            grupYetkiList = new List<GrupYetki>();
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                foreach (DataRow dr in dt.Rows)
                                {
                                    GrupYetki grupYetki = new GrupYetki(dr);
                                    grupYetkiList.Add(grupYetki);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return grupYetkiList;
        }

        public GrupYetki GrupYetkiGetirbyID(long id)
        {
            GrupYetki grupYetki = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = @"select gy.ID,
                                          g.ID as GrupID,
                                          g.Adi as GrupAdi,
                                          f.ID as FonksiyonID,
                                          f.Adi as FonksiyonAdi 
                                    from Grup g with(nolock)
                                    inner join GrupYetki gy with(nolock) on gy.GrupRef = g.ID
                                    inner join Fonksiyon f with(nolock) on f.ID=gy.fonksiyonRef 
                                    where gy.ID=@ID
                                    order by g.Adi,f.Adi";
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            cmd.Parameters.Add("@ID", id);
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count == 1)
                                grupYetki = new GrupYetki(dt.Rows[0]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return grupYetki;
        }

        public bool GrupYetkiVarMiKontrolYeni(long grupID, string fonksiyonAd)
        {
            bool sonuc = false;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = @"select gy.ID,
                                       g.ID as GrupID,
                                       g.Adi as GrupAdi,
                                       f.ID as FonksiyonID,
                                       f.Adi as FonksiyonAdi 
                                from Grup g with(nolock)
                                inner join GrupYetki gy with(nolock) on gy.GrupRef = g.ID
                                inner join Fonksiyon f with(nolock) on f.ID=gy.fonksiyonRef 
                                where g.ID=@grupID and f.Adi like @fonksiyonAdi
                                order by g.Adi,f.Adi";
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            cmd.Parameters.Add("@grupID", grupID);
                            cmd.Parameters.Add("@fonksiyonAdi", fonksiyonAd);
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count > 0)
                                sonuc = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return sonuc;
        }

        public void Dispose()
        {
        }
    }
}
